# this script retrieves price data from finance.yahoo.com 
# of all symbols in the table va_wikifolio.stockname

retrieveFrom <- "2000-01-01"

write_TS_to_table_stockprice <- function(TS, id) {
  sqlcmd <-function(row) {
    sql <- "INSERT INTO `va_wikifolio`.`stockprice` (`stock_id`, `date`, `open`, `high`, `low`, `close`, `volume`)"
    sql <- paste(sql, sprintf("VALUES ('%d', '%s', '%f', '%f', '%f', '%f', '%d')",
                              id, index(row), Op(row), Hi(row), Lo(row), Cl(row), Vo(row)))
    return (sql)
  }
  for (i in 1:nrow(TS)) {
    dbSendQuery(con, sqlcmd(TS[i,]))
  }
}

write_dividends_to_table <- function(TSDiv, id) {
  sqlcmd <-function(row) {
    sql <- "INSERT INTO `va_wikifolio`.`dividends` (`stock_id`, `date`, `dividend`)"
    sql <- paste(sql, sprintf("VALUES ('%d', '%s', '%f')",
                              id, index(row), row[,1]))
    return (sql)
  }
  for (i in 1:nrow(TSDiv)) {
    dbSendQuery(con, sqlcmd(TSDiv[i,]))
  }  
}

write_splits_to_table <- function(TSSplit, id) {
  sqlcmd <-function(row) {
    sql <- "INSERT INTO `va_wikifolio`.`splits` (`stock_id`, `date`, `ratio`)"
    sql <- paste(sql, sprintf("VALUES ('%d', '%s', '%f')",
                              id, index(row), row[,1]))
    return (sql)
  }
  for (i in 1:nrow(TSSplit)) {
    dbSendQuery(con, sqlcmd(TSSplit[i,]))
  }
}

# connect to database
con <- connectToVadb(dbname="va_wikifolio")

sqlcmd <-"SELECT `id`, `symbol` FROM stockname"
stocksym <- dbGetQuery(con, sqlcmd)

for (i in 1:nrow(stocksym)) {
  print (paste ("processing", stocksym[i,"symbol"]))

  Sys.sleep(2)
  TS <- getSymbols(stocksym[i,"symbol"], auto.assign=FALSE, from=retrieveFrom)
  if (is.xts(TS) & nrow(TS) > 1) {
    print(paste ("...retrieved", nrow(TS), "prices."))
    write_TS_to_table_stockprice (TS, stocksym[i,"id"])
  } else {
    print (paste("ERROR: failed to receive", stocksym[i,"symbol"]))
  }

  Sys.sleep(2)
  TSDiv <- getDividends(stocksym[i,"symbol"], auto.assign=FALSE, from=retrieveFrom)
  if (is.xts(TSDiv) & nrow(TSDiv) > 0) {
    print(paste ("...retrieved", nrow(TSDiv), "dividends."))
    write_dividends_to_table (TSDiv, stocksym[i,"id"])
  } else {
    print (paste("...no dividends retrieved"))
  }

  Sys.sleep(2)
  TSSplit <- getSplits(stocksym[i,"symbol"], auto.assign=FALSE, from=retrieveFrom)
  if (!is.na(TSSplit) && is.xts(TSSplit)) {
    if (nrow(TSSplit) > 0) {
      print(paste ("...retrieved", nrow(TSSplit), "splits."))
      write_splits_to_table (TSSplit, stocksym[i,"id"])      
    } else {
      print (paste("...no splits retrieved (but TSSplit was a xts)"))      
    }
  } else {
    print (paste("...no splits retrieved"))
  }
}
  
disconnectFromVadb(con)